﻿namespace FietsenManagementJB
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.OleDb;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;

    class ImportHelper
    {
        private SortableBindingList<Bike> importBikes;

        public ImportHelper()
        {
            importBikes = new SortableBindingList<Bike>();
        }

        public SortableBindingList<Bike> importProcedure()
        {
            var fileName = string.Format("{0}\\KogaHandel.xlsx", Directory.GetCurrentDirectory());
            var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";", fileName);

            var adapter = new OleDbDataAdapter("SELECT * FROM [blad1$]", connectionString);
            var ds = new DataSet();

            adapter.Fill(ds, "test");

            DataTable data = ds.Tables["test"];
            foreach (DataRow datarow in data.Rows)
            {
                var bike = new Bike();
                if (datarow["Type"].ToString() != "")
                {
                    bike.Type = datarow["Type"].ToString();
                    var frameAndGender = datarow["D of H"].ToString();
                    bike.Gender = frameAndGender.StartsWith("D") ? "Vrouw" : "Man";
                    bike.Frame = int.Parse(frameAndGender.Substring(1));
                    bike.BuyDate = DateTime.Parse(datarow["Datum aankoop"].ToString());
                    bike.BuyPrice = Math.Round(decimal.Parse(datarow["Inkoopprijs"].ToString()),2);
                    bike.LocationBought = datarow["Plaats aankoop"].ToString();
                    bike.SellDate = datarow["Datum verkoop"].ToString() != "" ? DateTime.Parse(datarow["Datum verkoop"].ToString()) : DateTime.MinValue;
                    bike.SellPrice = Math.Round(decimal.Parse(datarow["Verkoopprijs"].ToString()),2);
                    bike.InvestmentPrice = Math.Round(decimal.Parse(datarow["Investering"].ToString()),2);
                    importBikes.Add(bike);
                }
            }
            return importBikes;
        }
    }
}
